﻿using GPS.COMMON;
using GPS.Controllers.GPS_NghiepVu;
using GPS.DataAccessLayer;
using GPS.ENTITYS;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace GPS.GPS_DAL
{
    public class SOGIOHOC_GVPT_INFO
    {
        public DateTime? KH_LH_TUNGAY { get; set; }
        public DateTime? KH_LH_DENNGAY { get; set; }
        public decimal? HOURS { get; set; }
    }




    public class GPS_HocVienDAO
    {
        /// <summary>
        /// GPS_GETDS_KHOAHOC_HOCVIEN_TN
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_TEN"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_TN(string pDVGTVT_MA, string pKH_TEN)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN_TN"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        //giangtd3 -2017-09-09 GET_BCIDetail_GET_Detail_PT
        /// <summary>
        /// GEt GET_BCIDetail_BY_KHOAHOC
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GET_BCIDetail_GET_Detail_DiemDanh(string pKH_MA, string pDVGTVT_MA, string pHV_MA)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);

            var _pRET1 = new OracleParameter("pRET1", OracleDbType.RefCursor, ParameterDirection.Output); //pHV_MA
          
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GET_BCIDetail_GET_Detail_DD"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pHV_MA
                                                   , _pRET1
                                                   );

            return datas;
        }

        //giangtd3 -2017-09-09 GET_BCIDetail_GET_Detail_PT
        /// <summary>
        /// GEt GET_BCIDetail_BY_KHOAHOC
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GET_BCIDetail_GET_Detail_PT(string pKH_MA, string pDVGTVT_MA,string pPT_BIENSO)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_BIENSO = new OracleParameter("pPT_BIENSO", OracleDbType.Varchar2, (pPT_BIENSO == null ? DBNull.Value : (object)pPT_BIENSO), ParameterDirection.Input);
          
            var _pRET1 = new OracleParameter("pRET1", OracleDbType.RefCursor, ParameterDirection.Output); //PHUONGTIEN
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);//HOCVIENS
            var _pRET3 = new OracleParameter("pRET3", OracleDbType.RefCursor, ParameterDirection.Output);//GIANGVIENS
           
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GET_BCIDetail_GET_Detail_PT"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pPT_BIENSO
                                                   , _pRET1
                                                   , _pRET2
                                                   , _pRET3
                                                   );

            return datas;
        }

        //giangtd3 -2017-08-26 GET_BCIDetail_BY_KHOAHOC
        /// <summary>
        /// GEt GET_BCIDetail_BY_KHOAHOC
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GET_BCIDetail_BY_KHOAHOC(string pKH_MA,string pDVGTVT_MA)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET1 = new OracleParameter("pRET1", OracleDbType.RefCursor, ParameterDirection.Output); //KHOAHOC
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);//HOCVIENS
            var _pRET3 = new OracleParameter("pRET3", OracleDbType.RefCursor, ParameterDirection.Output);//PHUONGTIENS
            var _pRET4 = new OracleParameter("pRET4", OracleDbType.RefCursor, ParameterDirection.Output);//GIANGVIENS
            var _pRET5 = new OracleParameter("pRET5", OracleDbType.RefCursor, ParameterDirection.Output);//LYTHUYETS
            var _pRET6 = new OracleParameter("pRET6", OracleDbType.RefCursor, ParameterDirection.Output);//THUCHANHS

            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GET_BCIDetail_BY_KHOAHOC"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pRET1
                                                   , _pRET2
                                                   , _pRET3
                                                   , _pRET4
                                                   , _pRET5
                                                   , _pRET6
                                                   );

            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_SCVSHV --
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<SCVSHV> GPS_GET_SCVSHV()
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<SCVSHV>(@"BEGIN GPS_HOCVIEN.GPS_GET_SCVSHV(:pRET
                                                                                                        ); END;"
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// Search học viên GPS_SEARCH_KH_LH_1_TH Search--
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH1> GPS_SEARCH_KH_LH_1_TH(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA, DateTime? pKH_LH_TUNGAY, DateTime? pKH_LH_DENNGAY)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);
            var _pKH_LH_TUNGAY = new OracleParameter("pKH_LH_TUNGAY", OracleDbType.Date, (pKH_LH_TUNGAY == null ? DBNull.Value : (object)pKH_LH_TUNGAY), ParameterDirection.Input);
            var _pKH_LH_DENNGAY = new OracleParameter("pKH_LH_DENNGAY", OracleDbType.Date, (pKH_LH_DENNGAY == null ? DBNull.Value : (object)pKH_LH_DENNGAY), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH1>(@"BEGIN GPS_HOCVIEN.GPS_SEARCH_KH_LH_1_TH(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pKH_LH_TUNGAY
                                                                                                           ,:pKH_LH_DENNGAY
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pKH_LH_TUNGAY
                                                                                          , _pKH_LH_DENNGAY
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

      
        //Search
        /// <summary>
        /// Học viên thi tốt nghiệp GPS_GETDS_HOCVIEN_DU_TOTNGHIEP_SEARCH
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDHV_DDTTN> GPS_GETDS_HOCVIEN_DU_TOTNGHIEP_SEARCH(string pDVGTVT_MA, string pKH_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHANG_DT, decimal? pSO_QD_TN, DateTime? pNGAY_QD_TUNGAY, DateTime? pNGAY_QD_DENNGAY)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            //  var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHANG_DT = new OracleParameter("pHANG_DT", OracleDbType.Varchar2, (pHANG_DT == null ? DBNull.Value : (object)pHANG_DT), ParameterDirection.Input);
            var _pSO_QD_TN = new OracleParameter("pSO_QD_TN", OracleDbType.Decimal, (pSO_QD_TN == null ? DBNull.Value : (object)pSO_QD_TN), ParameterDirection.Input);
            var _pNGAY_QD_TUNGAY = new OracleParameter("pNGAY_QD_TUNGAY", OracleDbType.Date, (pNGAY_QD_TUNGAY == null ? DBNull.Value : (object)pNGAY_QD_TUNGAY), ParameterDirection.Input);
            var _pNGAY_QD_DENNGAY = new OracleParameter("pNGAY_QD_DENNGAY", OracleDbType.Date, (pNGAY_QD_DENNGAY == null ? DBNull.Value : (object)pNGAY_QD_DENNGAY), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV_DDTTN>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_TOTNGHIEP_S(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHANG_DT
                                                                                                            ,:pSO_QD_TN
                                                                                                            ,:pNGAY_QD_TUNGAY
                                                                                                            ,:pNGAY_QD_DENNGAY
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHANG_DT
                                                                                          , _pSO_QD_TN
                                                                                          , _pNGAY_QD_TUNGAY
                                                                                          , _pNGAY_QD_DENNGAY
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        //GPS_KH_LH_1_TH_DELETE
        public void GPS_KH_LH_1_TH_DELETE(string pKH_LH_DVGTVT_MA, string pKH_LH_KH_MA, string pKH_LH_GV_MA, string pKH_LH_PT_BIENSO, decimal? pID_TH)
        {

            var _pKH_LH_DVGTVT_MA = new OracleParameter("pKH_LH_DVGTVT_MA", OracleDbType.Varchar2, (pKH_LH_DVGTVT_MA == null ? DBNull.Value : (object)pKH_LH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_LH_KH_MA = new OracleParameter("pKH_LH_KH_MA", OracleDbType.Varchar2, (pKH_LH_KH_MA == null ? DBNull.Value : (object)pKH_LH_KH_MA), ParameterDirection.Input);
            var _pKH_LH_GV_MA = new OracleParameter("pKH_LH_GV_MA", OracleDbType.Varchar2, (pKH_LH_GV_MA == null ? DBNull.Value : (object)pKH_LH_GV_MA), ParameterDirection.Input);
            var _pKH_LH_PT_BIENSO = new OracleParameter("pKH_LH_PT_BIENSO", OracleDbType.Varchar2, (pKH_LH_PT_BIENSO == null ? DBNull.Value : (object)pKH_LH_PT_BIENSO), ParameterDirection.Input);
            var _pID_TH = new OracleParameter("pID_TH", OracleDbType.Decimal, (pID_TH == null ? DBNull.Value : (object)pID_TH), ParameterDirection.Input);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            OracleHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_KH_LH_1_TH_DELETE"
                                                                , _pKH_LH_DVGTVT_MA
                                                                , _pKH_LH_KH_MA
                                                                , _pKH_LH_GV_MA
                                                                , _pKH_LH_PT_BIENSO
                                                                , _pID_TH
                                                                );

        }

        //GPS_KH_LH_1_TH_UPDATE
        public void GPS_KH_LH_1_TH_UPDATE(CrudModelDKH_LH1_TH model)
        {

            var _pKH_LH_DVGTVT_MA = new OracleParameter("pKH_LH_DVGTVT_MA", OracleDbType.Varchar2, (model.KH_LH_DVGTVT_MA == null ? DBNull.Value : (object)model.KH_LH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_LH_KH_MA = new OracleParameter("pKH_LH_KH_MA", OracleDbType.Varchar2, (model.KH_LH_KH_MA == null ? DBNull.Value : (object)model.KH_LH_KH_MA), ParameterDirection.Input);
            var _pKH_LH_GV_MA = new OracleParameter("pKH_LH_GV_MA", OracleDbType.Varchar2, (model.KH_LH_GV_MA == null ? DBNull.Value : (object)model.KH_LH_GV_MA), ParameterDirection.Input);
            var _pKH_LH_MONHOC_MA = new OracleParameter("pKH_LH_MONHOC_MA", OracleDbType.Varchar2, (model.KH_LH_MONHOC_MA == null ? DBNull.Value : (object)model.KH_LH_MONHOC_MA), ParameterDirection.Input);
            var _pKH_LH_HTDT_MA = new OracleParameter("pKH_LH_HTDT_MA", OracleDbType.Varchar2, (model.KH_LH_HTDT_MA == null ? DBNull.Value : (object)model.KH_LH_HTDT_MA), ParameterDirection.Input);
            var _pKH_LH_PT_BIENSO = new OracleParameter("pKH_LH_PT_BIENSO", OracleDbType.Varchar2, (model.KH_LH_PT_BIENSO == null ? DBNull.Value : (object)model.KH_LH_PT_BIENSO), ParameterDirection.Input);
            var _pKH_LH_SOHOCVIEN = new OracleParameter("pKH_LH_SOHOCVIEN", OracleDbType.Varchar2, (model.KH_LH_SOHOCVIEN == null ? DBNull.Value : (object)model.KH_LH_SOHOCVIEN), ParameterDirection.Input);
            var _pKH_LH_THANG = new OracleParameter("pKH_LH_THANG", OracleDbType.Decimal, (model.KH_LH_THANG == null ? DBNull.Value : (object)model.KH_LH_THANG), ParameterDirection.Input);
            var _pKH_LH_TUAN = new OracleParameter("pKH_LH_TUAN", OracleDbType.Decimal, (model.KH_LH_TUAN == null ? DBNull.Value : (object)model.KH_LH_TUAN), ParameterDirection.Input);
            var _pKH_LH_TUNGAY = new OracleParameter("pKH_LH_TUNGAY", OracleDbType.Date, (model.KH_LH_TUNGAY == null ? DBNull.Value : (object)model.KH_LH_TUNGAY), ParameterDirection.Input);
            var _pKH_LH_DENNGAY = new OracleParameter("pKH_LH_DENNGAY=", OracleDbType.Date, (model.KH_LH_DENNGAY == null ? DBNull.Value : (object)model.KH_LH_DENNGAY), ParameterDirection.Input);
            var _pKH_LH_SOGIO = new OracleParameter("pKH_LH_SOGIO", OracleDbType.Decimal, (model.KH_LH_SOGIO == null ? DBNull.Value : (object)model.KH_LH_SOGIO), ParameterDirection.Input);
            var _pKH_LH_GDOAN_MA = new OracleParameter("pKH_LH_GDOAN_MA", OracleDbType.Varchar2, (model.KH_LH_GDOAN_MA == null ? DBNull.Value : (object)model.KH_LH_GDOAN_MA), ParameterDirection.Input);
            var _pKH_LH_KIEMTRA = new OracleParameter("pKH_LH_KIEMTRA ", OracleDbType.Varchar2, (model.KH_LH_KIEMTRA == null ? DBNull.Value : (object)model.KH_LH_KIEMTRA), ParameterDirection.Input);
            var _pKH_LH_MOTA = new OracleParameter("pKH_LH_MOTA", OracleDbType.Varchar2, (model.KH_LH_MOTA == null ? DBNull.Value : (object)model.KH_LH_MOTA), ParameterDirection.Input);
            var _pKH_LH_ACTIVE = new OracleParameter("pKH_LH_ACTIVE", OracleDbType.Varchar2, (model.KH_LH_ACTIVE == null ? DBNull.Value : (object)model.KH_LH_ACTIVE), ParameterDirection.Input);
            var _pKH_LH_NGAYNHAP = new OracleParameter("pKH_LH_NGAYNHAP", OracleDbType.Date, (model.KH_LH_NGAYNHAP == null ? DBNull.Value : (object)model.KH_LH_NGAYNHAP), ParameterDirection.Input);
            var _pKH_LH_NGUOINHAP = new OracleParameter("pKH_LH_NGUOINHAP", OracleDbType.Varchar2, (model.KH_LH_NGUOINHAP == null ? DBNull.Value : (object)model.KH_LH_NGUOINHAP), ParameterDirection.Input);
            var _pKH_LH_NGAYSUA = new OracleParameter("pKH_LH_NGAYSUA", OracleDbType.Date, (model.KH_LH_NGAYSUA == null ? DBNull.Value : (object)model.KH_LH_NGAYSUA), ParameterDirection.Input);
            var _pKH_LH_NGUOISUA = new OracleParameter("pKH_LH_NGUOISUA", OracleDbType.Varchar2, (model.KH_LH_NGUOISUA == null ? DBNull.Value : (object)model.KH_LH_NGUOISUA), ParameterDirection.Input);
            var _pID_TH = new OracleParameter("pID_TH", OracleDbType.Decimal, (model.ID_TH == null ? DBNull.Value : (object)model.ID_TH), ParameterDirection.Input);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            OracleHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_KH_LH_1_TH_UPDATE"
                                                                , _pKH_LH_DVGTVT_MA
                                                                , _pKH_LH_KH_MA
                                                                , _pKH_LH_GV_MA
                                                                , _pKH_LH_MONHOC_MA
                                                                , _pKH_LH_HTDT_MA
                                                                , _pKH_LH_PT_BIENSO
                                                                , _pKH_LH_SOHOCVIEN
                                                                , _pKH_LH_THANG
                                                                , _pKH_LH_TUAN
                                                                , _pKH_LH_TUNGAY
                                                                , _pKH_LH_DENNGAY
                                                                , _pKH_LH_SOGIO
                                                                , _pKH_LH_GDOAN_MA
                                                                , _pKH_LH_KIEMTRA
                                                                , _pKH_LH_MOTA
                                                                , _pKH_LH_ACTIVE
                                                                , _pKH_LH_NGAYNHAP
                                                                , _pKH_LH_NGUOINHAP
                                                                , _pKH_LH_NGAYSUA
                                                                , _pKH_LH_NGUOISUA
                                                                , _pID_TH
                                                                );

        }

        //GPS_KH_LH_1_TH_INSERT
        public void GPS_KH_LH_1_TH_INSERT(CrudModelDKH_LH1_TH model)
        {

            var _pKH_LH_DVGTVT_MA = new OracleParameter("pKH_LH_DVGTVT_MA", OracleDbType.Varchar2, (model.KH_LH_DVGTVT_MA == null ? DBNull.Value : (object)model.KH_LH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_LH_KH_MA = new OracleParameter("pKH_LH_KH_MA", OracleDbType.Varchar2, (model.KH_LH_KH_MA == null ? DBNull.Value : (object)model.KH_LH_KH_MA), ParameterDirection.Input);
            var _pKH_LH_GV_MA = new OracleParameter("pKH_LH_GV_MA", OracleDbType.Varchar2, (model.KH_LH_GV_MA == null ? DBNull.Value : (object)model.KH_LH_GV_MA), ParameterDirection.Input);
            var _pKH_LH_MONHOC_MA = new OracleParameter("pKH_LH_MONHOC_MA", OracleDbType.Varchar2, (model.KH_LH_MONHOC_MA == null ? DBNull.Value : (object)model.KH_LH_MONHOC_MA), ParameterDirection.Input);
            var _pKH_LH_HTDT_MA = new OracleParameter("pKH_LH_HTDT_MA", OracleDbType.Varchar2, (model.KH_LH_HTDT_MA == null ? DBNull.Value : (object)model.KH_LH_HTDT_MA), ParameterDirection.Input);
            var _pKH_LH_PT_BIENSO = new OracleParameter("pKH_LH_PT_BIENSO", OracleDbType.Varchar2, (model.KH_LH_PT_BIENSO == null ? DBNull.Value : (object)model.KH_LH_PT_BIENSO), ParameterDirection.Input);
            var _pKH_LH_SOHOCVIEN = new OracleParameter("pKH_LH_SOHOCVIEN", OracleDbType.Varchar2, (model.KH_LH_SOHOCVIEN == null ? DBNull.Value : (object)model.KH_LH_SOHOCVIEN), ParameterDirection.Input);
            var _pKH_LH_THANG = new OracleParameter("pKH_LH_THANG", OracleDbType.Decimal, (model.KH_LH_THANG == null ? DBNull.Value : (object)model.KH_LH_THANG), ParameterDirection.Input);
            var _pKH_LH_TUAN = new OracleParameter("pKH_LH_TUAN", OracleDbType.Decimal, (model.KH_LH_TUAN == null ? DBNull.Value : (object)model.KH_LH_TUAN), ParameterDirection.Input);
            var _pKH_LH_TUNGAY = new OracleParameter("pKH_LH_TUNGAY", OracleDbType.Date, (model.KH_LH_TUNGAY == null ? DBNull.Value : (object)model.KH_LH_TUNGAY), ParameterDirection.Input);
            var _pKH_LH_DENNGAY = new OracleParameter("pKH_LH_DENNGAY=", OracleDbType.Date, (model.KH_LH_DENNGAY == null ? DBNull.Value : (object)model.KH_LH_DENNGAY), ParameterDirection.Input);
            var _pKH_LH_SOGIO = new OracleParameter("pKH_LH_SOGIO", OracleDbType.Decimal, (model.KH_LH_SOGIO == null ? DBNull.Value : (object)model.KH_LH_SOGIO), ParameterDirection.Input);
            var _pKH_LH_GDOAN_MA = new OracleParameter("pKH_LH_GDOAN_MA", OracleDbType.Varchar2, (model.KH_LH_GDOAN_MA == null ? DBNull.Value : (object)model.KH_LH_GDOAN_MA), ParameterDirection.Input);
            var _pKH_LH_KIEMTRA = new OracleParameter("pKH_LH_KIEMTRA ", OracleDbType.Varchar2, (model.KH_LH_KIEMTRA == null ? DBNull.Value : (object)model.KH_LH_KIEMTRA), ParameterDirection.Input);
            var _pKH_LH_MOTA = new OracleParameter("pKH_LH_MOTA", OracleDbType.Varchar2, (model.KH_LH_MOTA == null ? DBNull.Value : (object)model.KH_LH_MOTA), ParameterDirection.Input);
            var _pKH_LH_ACTIVE = new OracleParameter("pKH_LH_ACTIVE", OracleDbType.Varchar2, (model.KH_LH_ACTIVE == null ? DBNull.Value : (object)model.KH_LH_ACTIVE), ParameterDirection.Input);
            var _pKH_LH_NGAYNHAP = new OracleParameter("pKH_LH_NGAYNHAP", OracleDbType.Date, (model.KH_LH_NGAYNHAP == null ? DBNull.Value : (object)model.KH_LH_NGAYNHAP), ParameterDirection.Input);
            var _pKH_LH_NGUOINHAP = new OracleParameter("pKH_LH_NGUOINHAP", OracleDbType.Varchar2, (model.KH_LH_NGUOINHAP == null ? DBNull.Value : (object)model.KH_LH_NGUOINHAP), ParameterDirection.Input);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            OracleHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_KH_LH_1_TH_INSERT"
                                                                , _pKH_LH_DVGTVT_MA
                                                                , _pKH_LH_KH_MA
                                                                , _pKH_LH_GV_MA
                                                                , _pKH_LH_MONHOC_MA
                                                                , _pKH_LH_HTDT_MA
                                                                , _pKH_LH_PT_BIENSO
                                                                , _pKH_LH_SOHOCVIEN
                                                                , _pKH_LH_THANG
                                                                , _pKH_LH_TUAN
                                                                , _pKH_LH_TUNGAY
                                                                , _pKH_LH_DENNGAY
                                                                , _pKH_LH_SOGIO
                                                                , _pKH_LH_GDOAN_MA
                                                                , _pKH_LH_KIEMTRA
                                                                , _pKH_LH_MOTA
                                                                , _pKH_LH_ACTIVE
                                                                , _pKH_LH_NGAYNHAP
                                                                , _pKH_LH_NGUOINHAP
                                                                );

        }

        /// <summary>
        /// GEt học viên GPS_GET_GV_IN_KH_TH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GPS_GET_GV_IN_KH_TH(string pDVGTVT_MA, string pKH_MA)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GPS_GET_GV_IN_KH_TH"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pRET
                                                   , _pRET2
                                                   );

            return datas;
        }

        //GPS_KH_LH_1_TH_GET_BY_IDTH
        public List<ModelDKH_LH1> GPS_KH_LH_1_TH_GET_BY_IDTH(string pKH_LH_DVGTVT_MA, string pKH_LH_KH_MA, decimal? pID_TH)
        {

            var _pKH_LH_DVGTVT_MA = new OracleParameter("pKH_LH_DVGTVT_MA", OracleDbType.Varchar2, (pKH_LH_DVGTVT_MA == null ? DBNull.Value : (object)pKH_LH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_LH_KH_MA = new OracleParameter("pKH_LH_KH_MA", OracleDbType.Varchar2, (pKH_LH_KH_MA == null ? DBNull.Value : (object)pKH_LH_KH_MA), ParameterDirection.Input);
            var _pID_TH = new OracleParameter("pID_TH", OracleDbType.Decimal, (pID_TH == null ? DBNull.Value : (object)pID_TH), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_KH_LH_1_TH_GET_BY_IDTH"
                                                                , _pKH_LH_DVGTVT_MA
                                                                , _pKH_LH_KH_MA
                                                                , _pID_TH
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<ModelDKH_LH1>(ds.Tables[0]);
            return datas;

        }

        //GPS_KH_LH_1_TH_DETAIL
        public DKH_LH1 GPS_KH_LH_1_TH_DETAIL(string pKH_LH_DVGTVT_MA, string pKH_LH_KH_MA, decimal? pID_TH)
        {

            var _pKH_LH_DVGTVT_MA = new OracleParameter("pKH_LH_DVGTVT_MA", OracleDbType.Varchar2, (pKH_LH_DVGTVT_MA == null ? DBNull.Value : (object)pKH_LH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_LH_KH_MA = new OracleParameter("pKH_LH_KH_MA", OracleDbType.Varchar2, (pKH_LH_KH_MA == null ? DBNull.Value : (object)pKH_LH_KH_MA), ParameterDirection.Input);
            var _pID_TH = new OracleParameter("pID_TH", OracleDbType.Decimal, (pID_TH == null ? DBNull.Value : (object)pID_TH), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_KH_LH_1_TH_DETAIL"
                                                                , _pKH_LH_DVGTVT_MA
                                                                , _pKH_LH_KH_MA
                                                                , _pID_TH
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataRowToObject<DKH_LH1>(ds.Tables[0].Rows[0]);
            return datas;

        }

        /// <summary>
        /// GEt học viên GPS_GET_KH_LH thuc hanh
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH1> GPS_GET_KH_LH_1_TH(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH1>(@"BEGIN GPS_HOCVIEN.GPS_GET_KH_LH_1_TH(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// Search học viên GPS_GET_KH_LH -- ly thuyet Search
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH1> GPS_GET_KH_LH_1_LT_S(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA, DateTime? pKH_LH_TUNGAY, DateTime? pKH_LH_DENNGAY)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);
            var _pKH_LH_TUNGAY = new OracleParameter("pKH_LH_TUNGAY", OracleDbType.Date, (pKH_LH_TUNGAY == null ? DBNull.Value : (object)pKH_LH_TUNGAY), ParameterDirection.Input);
            var _pKH_LH_DENNGAY = new OracleParameter("pKH_LH_DENNGAY", OracleDbType.Date, (pKH_LH_DENNGAY == null ? DBNull.Value : (object)pKH_LH_DENNGAY), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH1>(@"BEGIN GPS_HOCVIEN.GPS_GET_KH_LH_1_LT_S(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pKH_LH_TUNGAY
                                                                                                           ,:pKH_LH_DENNGAY
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pKH_LH_TUNGAY
                                                                                          , _pKH_LH_DENNGAY
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_KH_LH -- ly thuyet
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH1> GPS_GET_KH_LH_1_LT(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH1>(@"BEGIN GPS_HOCVIEN.GPS_GET_KH_LH_1_LT(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_GV_IN_KH_LT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GPS_GET_GV_IN_KH_LT(string pDVGTVT_MA, string pKH_MA)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GPS_GET_GV_IN_KH_LT"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pRET
                                                   , _pRET2
                                                   );

            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_KHOAHOC_AUTO
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_AUTO(string pDVGTVT_MA)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_AUTO"
                                                                , _pDVGTVT_MA
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        //GPS_GET_PT_AUTO_NOTIN_KH
        /// <summary>
        /// GEt học viên GPS_GET_PT_AUTO_NOTIN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_PTinKH> GPS_GET_PT_AUTO_NOTIN_KH(string pDVGTVT_MA, string pKH_MA)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                  , "GPS_HOCVIEN.GPS_GET_PT_AUTO_NOTIN_KH"
                                                  , _pDVGTVT_MA
                                                  , _pKH_MA
                                                  , _pRET
                                                  );
            return GPS_Library.MapDataTableToList<JsonDataDKH_PTinKH>(datas.Tables[0]);
        }


        //GPS_GET_PT_AUTO_IN_KH
        /// <summary>
        /// GEt học viên GPS_GET_PT_AUTO_IN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDateDKH_PT_AUTO> GPS_GET_PT_AUTO_IN_KH(string pDVGTVT_MA, string pKH_MA)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                  , "GPS_HOCVIEN.GPS_GET_PT_AUTO_IN_KH"
                                                  , _pDVGTVT_MA
                                                  , _pKH_MA
                                                  , _pRET
                                                  );
            return GPS_Library.MapDataTableToList<JsonDateDKH_PT_AUTO>(datas.Tables[0]);
        }


        //GPS_GET_PT_NOTIN_KH
        /// <summary>
        /// GEt học viên GPS_GET_PT_NOTIN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_PTinKH> GPS_GET_PT_NOTIN_KH(string pDVGTVT_MA, string pKH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_PTinKH>(@"BEGIN GPS_HOCVIEN.GPS_GET_PT_NOTIN_KH(:pDVGTVT_MA,:pKH_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        //GPS_GET_HV_BY_KH_MA
        /// <summary>
        /// GEt học viên GPS_GET_HV_BY_KH_MA
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DHV> GPS_GET_HV_BY_KH_MA(string pKH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DHV>(@"BEGIN GPS_HOCVIEN.GPS_GET_HV_BY_KH_MA(:pKH_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pKH_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        //GPS_GET_PT_IN_KH_BY_HT_TH
        /// <summary>
        /// GEt học viên GPS_GET_PT_IN_KH_BY_HT_TH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_GVinPT> GPS_GET_PT_IN_KH_BY_HT_TH(string pDVGTVT_MA, string pKH_MA, string pMA_HT)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            // var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pMA_HT = new OracleParameter("pMA_HT", OracleDbType.Varchar2, (pMA_HT == null ? DBNull.Value : (object)pMA_HT), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_GVinPT>(@"BEGIN GPS_HOCVIEN.GPS_GET_GV_IN_KH_BY_HT_TH(:pDVGTVT_MA,:pKH_MA,:pMA_HT
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pMA_HT
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        //GPS_GET_PT_IN_KH_BY_HT
        /// <summary>
        /// GEt học viên GPS_GET_GV_IN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_GVinPT> GPS_GET_GV_IN_KH_BY_HT(string pDVGTVT_MA, string pKH_MA, string pPT_MA, string pMA_HT)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pMA_HT = new OracleParameter("pMA_HT", OracleDbType.Varchar2, (pMA_HT == null ? DBNull.Value : (object)pMA_HT), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_GVinPT>(@"BEGIN GPS_HOCVIEN.GPS_GET_GV_IN_KH_BY_HT(:pDVGTVT_MA,:pKH_MA,:pPT_MA,:pMA_HT
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pPT_MA
                                                                                          , _pMA_HT
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_KH_LH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH1> GPS_GET_KH_LH_1(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH1>(@"BEGIN GPS_HOCVIEN.GPS_GET_KH_LH_1(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_DELETE_DKH_HV
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public void GPS_DELETE_DKH_HV(string pDVGTVT_MA, string pKH_MA, string pPT_MA, string pHV_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);

            var datas = OracleHelper.ExecuteNonQuery(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                    , "GPS_HOCVIEN.GPS_DELETE_DKH_HV"
                                                    , _pDVGTVT_MA
                                                    , _pKH_MA
                                                    , _pPT_MA
                                                    , _pHV_MA
                                                    );

        }

        //
        /// <summary>
        /// GEt học viên GPS_DELETE_DKH_GV
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public void GPS_DELETE_DKH_GV(string pDVGTVT_MA, string pKH_MA, string pPT_MA, string pGV_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);

            var datas = OracleHelper.ExecuteNonQuery(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                    , "GPS_HOCVIEN.GPS_DELETE_DKH_GV"
                                                    , _pDVGTVT_MA
                                                    , _pKH_MA
                                                    , _pPT_MA
                                                    , _pGV_MA
                                                    );

        }

        /// <summary>
        /// GEt học viên GPS_DELETE_DKH_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public void GPS_DELETE_DKH_PT(string pDVGTVT_MA, string pKH_MA, string pPT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);

            var datas = OracleHelper.ExecuteNonQuery(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                    , "GPS_HOCVIEN.GPS_DELETE_DKH_PT"
                                                    , _pDVGTVT_MA
                                                    , _pKH_MA
                                                    , _pPT_MA
                                                    );

        }


        /// <summary>
        /// GEt học viên GPS_GET_HV_NOTIN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DHV> GPS_GET_HV_NOTIN_PT(string pDVGTVT_MA, string pKH_MA, string pPT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DHV>(@"BEGIN GPS_HOCVIEN.GPS_GET_HV_NOTIN_PT(:pDVGTVT_MA,:pKH_MA,:pPT_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pPT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        /// <summary>
        /// GEt học viên GPS_GET_HV_IN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_HVinKH> GPS_GET_HV_IN_PT(string pDVGTVT_MA, string pKH_MA, string pPT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_HVinKH>(@"BEGIN GPS_HOCVIEN.GPS_GET_HV_IN_PT(:pWHERE,:pKH_MA,:pPT_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pPT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        /// <summary>
        /// GEt học viên GPS_GET_PT_IN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GPS_GET_GV_IN_NOTIN_KH(string pDVGTVT_MA, string pKH_MA, string pIS2)
        {

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pIS2 = new OracleParameter("pIS2", OracleDbType.Varchar2, (pIS2 == null ? DBNull.Value : (object)pIS2), ParameterDirection.Input);

            var _pRET1 = new OracleParameter("pRET1", OracleDbType.RefCursor, ParameterDirection.Output);
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                    , "GPS_HOCVIEN.GPS_GET_GV_IN_NOTIN_KH"
                                                    , _pDVGTVT_MA
                                                    , _pKH_MA
                                                    , _pIS2
                                                    , _pRET1
                                                    , _pRET2
                                                    );
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_PT_IN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public DataSet GPS_GET_PT_IN_KH(string pDVGTVT_MA, string pKH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var _pRET2 = new OracleParameter("pRET2", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure
                                                   , "GPS_HOCVIEN.GPS_GET_PT_IN_KH"
                                                   , _pDVGTVT_MA
                                                   , _pKH_MA
                                                   , _pRET
                                                   , _pRET2
                                                   );

            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_HV_IN_KH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_HVinKH> GPS_GET_HV_IN_KH(string pWHERE, string pKH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pWHERE = new OracleParameter("pWHERE", OracleDbType.Varchar2, (pWHERE == null ? DBNull.Value : (object)pWHERE), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_HVinKH>(@"BEGIN GPS_HOCVIEN.GPS_GET_HV_IN_KH(:pWHERE,:pKH_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pWHERE
                                                                                          , _pKH_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt giảng viên GPS_GET_GV_NOTIN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DGV> GPS_GET_GV_NOTIN_PT(string pDVGTVT_MA, string pKH_MA, string pPT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DGV>(@"BEGIN GPS_HOCVIEN.GPS_GET_GV_NOTIN_PT(:pDVGTVT_MA,:pKH_MA,:pPT_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pPT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt giang viên GPS_GET_GV_IN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_GVinPT> GPS_GET_GV_IN_PT(string pDVGTVT_MA, string pKH_MA, string pPT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_GVinPT>(@"BEGIN GPS_HOCVIEN.GPS_GET_GV_IN_PT_2(:pWHERE,:pKH_MA,:pPT_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pPT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_GV_IN_PT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<JsonDataDKH_GVinPT> GPS_GET_GV_IN_PT(string pWHERE, string pKH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pWHERE = new OracleParameter("pWHERE", OracleDbType.Varchar2, (pWHERE == null ? DBNull.Value : (object)pWHERE), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<JsonDataDKH_GVinPT>(@"BEGIN GPS_HOCVIEN.GPS_GET_GV_IN_PT(:pWHERE,:pKH_MA
                                                                                                       ,:pRET
                                                                                                       ); END;"
                                                                                          , _pWHERE
                                                                                          , _pKH_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_KH_LH
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDKH_LH> GPS_GET_KH_LH(string pDVGTVT_MA, string pKH_MA, string pGV_MA, string pPT_MA, string pHANGDT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (pGV_MA == null ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (pPT_MA == null ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pHANGDT_MA = new OracleParameter("pHANGDT_MA", OracleDbType.Varchar2, (pHANGDT_MA == null ? DBNull.Value : (object)pHANGDT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDKH_LH>(@"BEGIN GPS_HOCVIEN.GPS_GET_KH_LH(:pDVGTVT_MA
                                                                                                           ,:pKH_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pHANGDT_MA
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pHANGDT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GET_SOGIOHOC_GVPT
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public SOGIOHOC_GVPT_INFO GPS_GET_SOGIOHOC_GVPT(string pDVGTVT_MA, string pGV_MA, string pPT_MA, DateTime? pNGAY)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pGV_MA = new OracleParameter("pGV_MA", OracleDbType.Varchar2, (string.IsNullOrWhiteSpace(pGV_MA) ? DBNull.Value : (object)pGV_MA), ParameterDirection.Input);
            var _pPT_MA = new OracleParameter("pPT_MA", OracleDbType.Varchar2, (string.IsNullOrWhiteSpace(pPT_MA) ? DBNull.Value : (object)pPT_MA), ParameterDirection.Input);
            var _pNGAY = new OracleParameter("pNGAY", OracleDbType.Date, (pNGAY == null ? DBNull.Value : (object)pNGAY), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<SOGIOHOC_GVPT_INFO>(@"BEGIN GPS_HOCVIEN.GPS_GET_SOGIOHOC_GVPT(:pDVGTVT_MA
                                                                                                           ,:pGV_MA
                                                                                                           ,:pPT_MA
                                                                                                           ,:pNGAY
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pGV_MA
                                                                                          , _pPT_MA
                                                                                          , _pNGAY
                                                                                          , _pRET
                                                                                        ).ToList();
            if (datas != null && datas.Count > 0)
                return datas[0];
            else
                return null;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_KHOAHOC_HOCVIEN_LH1
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_LH1(string pDVGTVT_MA, string pKH_TEN)
        {
            //DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN_LH1"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_KHOAHOC_HOCVIEN_LH1
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_LH_TC(string pDVGTVT_MA, string pKH_TEN)
        {
            //DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HVIEN_LH_TC"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_KHOAHOC_HOCVIEN
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_LH(string pDVGTVT_MA)
        {
            //DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);

            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN_LH"
                                                                , _pDVGTVT_MA
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_KHOAHOC_HOCVIEN
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN(string pDVGTVT_MA)
        {
            //DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            //            var datas = _db.DbContext.Database.SqlQuery<DKH>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN(:pDVGTVT_MA
            //                                                                                                           ,:pRET
            //                                                                                                            ); END;"
            //                                                                                          , _pDVGTVT_MA
            //                                                                                          , _pRET
            //                                                                                        ).ToList();
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN"
                                                                , _pDVGTVT_MA
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN1(string pDVGTVT_MA)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN1"
                                                                , _pDVGTVT_MA
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_BCI(string pDVGTVT_MA, string pKH_TEN)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN_BCI"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN1(string pDVGTVT_MA,string pKH_TEN)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN1"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        public List<DKH> GPS_GETDS_KHOAHOC_HOCVIEN_TC(string pDVGTVT_MA, string pKH_TEN)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (pKH_TEN == null ? DBNull.Value : (object)pKH_TEN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            var ds = OracleHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure
                                                                , "GPS_HOCVIEN.GPS_GETDS_KHOAHOC_HOCVIEN_TC"
                                                                , _pDVGTVT_MA
                                                                , _pKH_TEN
                                                                , _pRET
                                                                );
            var datas = GPS_Library.MapDataTableToList<DKH>(ds.Tables[0]);
            return datas;
        }

        /// <summary>
        /// GEt học viên GPS_GETDS_HOCVIEN_KYTRUOC
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDHV_KYTRUOC> GPS_GETDS_HOCVIEN_KYTRUOC(string pHV_CMND, string pKH_MA, string pDVGTVT_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pLH_MA = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pKH_MA == null ? DBNull.Value : (object)pKH_MA), ParameterDirection.Input);
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV_KYTRUOC>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_KYTRUOC(:pHV_CMND
                                                                                                           ,:pKH_MA
                                                                                                           ,:pDVGTVT_MA
                                                                                                           ,:pRET
                                                                                                            ); END;"
                                                                                          , _pLH_MA
                                                                                          , _pKH_MA
                                                                                          , _pDVGTVT_MA
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        //        /// <summary>
        //        /// GEt học viên GPS_GETDS_HOCVIEN_KYTRUOC
        //        /// </summary>
        //        /// <param name="pHV_CMND"></param>
        //        /// <returns></returns>
        //        public List<ModelDHV_KYTRUOC> GPS_GETDS_HOCVIEN_KYTRUOC(string pHV_CMND)
        //        {
        //            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

        //            var _pLH_MA = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);

        //            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
        //            var datas = _db.DbContext.Database.SqlQuery<ModelDHV_KYTRUOC>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_KYTRUOC(:pHV_CMND
        //                                                                                                           ,:pRET); END;"
        //                                                                                          , _pLH_MA
        //                                                                                          , _pRET
        //                                                                                        ).ToList();
        //            return datas;
        //        }

        /// <summary>
        /// GEt học viên thuộc phương tiện
        /// </summary>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<DHV> GPS_GETDS_PHUONGTIEN_HOCVIEN(string pLH_MA, string pLH_BIENSO)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pLH_MA = new OracleParameter("pLH_MA", OracleDbType.Varchar2, (pLH_MA == null ? DBNull.Value : (object)pLH_MA), ParameterDirection.Input);
            var _pLH_BIENSO = new OracleParameter("pLH_BIENSO", OracleDbType.Varchar2, (pLH_BIENSO == null ? DBNull.Value : (object)pLH_BIENSO), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_PHUONGTIEN_HOCVIEN(:pLH_MA
                                                                                                           ,:pLH_BIENSO
                                                                                                           ,:pRET); END;"
                                                                                          , _pLH_MA
                                                                                          , _pLH_BIENSO
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        /// <summary>
        /// Get học viên kỳ trước
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <param name="pHV_GIAIDOAN_MA"></param>
        /// <param name="pHV_NGAY_DD_TU"></param>
        /// <param name="pHV_NGAY_DD_DEN"></param>
        /// <returns></returns>
        public List<DHV> GPS_GETDS_KYTRUOC_HOCVIEN(string pHV_CMND)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_KYTRUOC_HOCVIEN(:pHV_CMND
                                                                                                           ,:pRET); END;"
                                                                                          , _pHV_CMND
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }


        /// <summary>
        /// Get học viên điểm danh
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ViewDKH_DD> GPS_GETDS_DIEMDANH_HOCVIEN(string pDVGTVT_MA, string pKH_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHV_GIAIDOAN_MA, DateTime? pHV_NGAY_DD_TU, DateTime? pHV_NGAY_DD_DEN)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            //  var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHV_GIAIDOAN_MA = new OracleParameter("pHV_GIAIDOAN_MA", OracleDbType.Varchar2, (pHV_GIAIDOAN_MA == null ? DBNull.Value : (object)pHV_GIAIDOAN_MA), ParameterDirection.Input);
            var _pHV_NGAY_DD_TU = new OracleParameter("pHV_NGAY_DD_TU", OracleDbType.Date, (pHV_NGAY_DD_TU == null ? DBNull.Value : (object)pHV_NGAY_DD_TU), ParameterDirection.Input);
            var _pHV_NGAY_DD_DEN = new OracleParameter("pHV_NGAY_DD_DEN", OracleDbType.Date, (pHV_NGAY_DD_DEN == null ? DBNull.Value : (object)pHV_NGAY_DD_DEN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ViewDKH_DD>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_DIEMDANH_HOCVIEN(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHV_GIAIDOAN_MA
                                                                                                            ,:pHV_NGAY_DD_TU
                                                                                                            ,:pHV_NGAY_DD_DEN
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHV_GIAIDOAN_MA
                                                                                          , _pHV_NGAY_DD_TU
                                                                                          , _pHV_NGAY_DD_DEN
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }



        /// <summary>
        /// Học viên thi tốt nghiệp
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDHV_DDTTN> GPS_GETDS_HOCVIEN_DU_TOTNGHIEP(string pDVGTVT_MA, string pKH_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHANG_DT, decimal? pSO_QD_TN)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            //  var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHANG_DT = new OracleParameter("pHANG_DT", OracleDbType.Varchar2, (pHANG_DT == null ? DBNull.Value : (object)pHANG_DT), ParameterDirection.Input);
            var _pSO_QD_TN = new OracleParameter("pSO_QD_TN", OracleDbType.Decimal, (pSO_QD_TN == null ? DBNull.Value : (object)pSO_QD_TN), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV_DDTTN>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_DU_TOTNGHIEP(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHANG_DT
                                                                                                            ,:pSO_QD_TN
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHANG_DT
                                                                                          , _pSO_QD_TN
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }
        /// <summary>
        /// Get danh sách học viên
        /// </summary>
        /// <param name="pHV_DVGTVT_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_KH_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_SOCMT"></param>
        /// <param name="pHV_HANGDT_MA"></param>
        /// <param name="pHV_HS_HANGGPLXDACO"></param>
        /// <param name="pROW"></param>
        /// <returns></returns>

        public List<CrudModelITHHOCVIEN> GPS_GETDS_SEARCH_HOCVIEN1(string pHV_DVGTVT_MA, string pHV_MA, string pHV_KH_MA, string pHV_HOTEN, string pHV_SOCMT, string pHV_HANGDT_MA, string pHV_HS_HANGGPLXDACO, string pROW)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pHV_DVGTVT_MA == null ? DBNull.Value : (object)pHV_DVGTVT_MA), ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA.Trim()), ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (pHV_KH_MA == null ? DBNull.Value : (object)pHV_KH_MA.Trim()), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN.Trim()), ParameterDirection.Input);
            var _pHV_SOCMT = new OracleParameter("pHV_SOCMT", OracleDbType.Varchar2, (pHV_SOCMT == null ? DBNull.Value : (object)pHV_SOCMT.Trim()), ParameterDirection.Input);
            var _pHV_HANGDT_MA = new OracleParameter("pHV_HANGDT_MA", OracleDbType.Varchar2, (pHV_HANGDT_MA == null ? DBNull.Value : (object)pHV_HANGDT_MA), ParameterDirection.Input);
            var _pHV_HS_HANGGPLXDACO = new OracleParameter("pHV_HS_HANGGPLXDACO", OracleDbType.Varchar2, (pHV_HS_HANGGPLXDACO == null ? DBNull.Value : (object)pHV_HS_HANGGPLXDACO), ParameterDirection.Input);
            var _pROW = new OracleParameter("pROW", OracleDbType.Decimal, (pROW == null ? DBNull.Value : (object)pROW), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<CrudModelITHHOCVIEN>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_SEARCH_HOCVIEN1(
                                                                                                     :pDVGTVT_MA
                                                                                                    ,:pHV_MA
                                                                                                    ,:pKH_MA
                                                                                                    ,:pHV_HOTEN
                                                                                                    ,:pHV_SOCMT
                                                                                                    ,:pHV_HANGDT_MA
                                                                                                    ,:pHV_HS_HANGGPLXDACO
                                                                                                    ,:pROW
                                                                                                    ,:pRET); END;"
                                                                                                                            , _pDVGTVT_MA
                                                                                                                            , _pHV_MA
                                                                                                                            , _pKH_MA
                                                                                                                            , _pHV_HOTEN
                                                                                                                            , _pHV_SOCMT
                                                                                                                            , _pHV_HANGDT_MA
                                                                                                                            , _pHV_HS_HANGGPLXDACO
                                                                                                                            , _pROW
                                                                                                                            , _pRET).ToList();
            return datas;
        }


        /// <summary>
        /// GEt kết quả TN học viên Search
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDDTTN_KQTN> GPS_GETDS_KQTN_HOCVIEN_S(string pDVGTVT_MA, string pKH_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHANG_DT, decimal? pSO_QD_TN, DateTime? pNGAY_KQTN_TUNGAY, DateTime? pNGAY_KQTN_DENNGAY)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            //  var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHANG_DT = new OracleParameter("pHANG_DT", OracleDbType.Varchar2, (pHANG_DT == null ? DBNull.Value : (object)pHANG_DT), ParameterDirection.Input);
            var _pSO_QD_TN = new OracleParameter("pSO_QD_TN", OracleDbType.Decimal, (pSO_QD_TN == null ? DBNull.Value : (object)pSO_QD_TN), ParameterDirection.Input);
            var _pNGAY_KQTN_TUNGAY = new OracleParameter("pNGAY_KQTN_TUNGAY", OracleDbType.Date, (pNGAY_KQTN_TUNGAY == null ? DBNull.Value : (object)pNGAY_KQTN_TUNGAY), ParameterDirection.Input);
            var _pNGAY_KQTN_DENNGAY = new OracleParameter("pNGAY_KQTN_DENNGAY", OracleDbType.Date, (pNGAY_KQTN_DENNGAY == null ? DBNull.Value : (object)pNGAY_KQTN_DENNGAY), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDDTTN_KQTN>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_KQTN_HOCVIEN_S(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHANG_DT
                                                                                                            ,:pSO_QD_TN
                                                                                                            ,:pNGAY_KQTN_TUNGAY
                                                                                                            ,:pNGAY_KQTN_DENNGAY
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHANG_DT
                                                                                          , _pSO_QD_TN
                                                                                          , _pNGAY_KQTN_TUNGAY
                                                                                          , _pNGAY_KQTN_DENNGAY
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// GEt kết quả TN học viên
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDDTTN_KQTN> GPS_GETDS_KQTN_HOCVIEN(string pDVGTVT_MA, string pKH_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHANG_DT, decimal? pSO_QD_TN)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            //  var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHANG_DT = new OracleParameter("pHANG_DT", OracleDbType.Varchar2, (pHANG_DT == null ? DBNull.Value : (object)pHANG_DT), ParameterDirection.Input);
            var _pSO_QD_TN = new OracleParameter("pSO_QD_TN", OracleDbType.Decimal, (pSO_QD_TN == null ? DBNull.Value : (object)pSO_QD_TN), ParameterDirection.Input);

            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDDTTN_KQTN>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_KQTN_HOCVIEN(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHANG_DT
                                                                                                            ,:pSO_QD_TN
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHANG_DT
                                                                                          , _pSO_QD_TN
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// Nhập điểm sát hạch học viên
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ViewDDTSH_KQSH> GPS_GETDS_KQSH_HOCVIEN(string pDVGTVT_MA, string pKH_MA, string pKH_LOP_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pHV_SBD)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pHV_SBD = new OracleParameter("pHV_SBD", OracleDbType.Varchar2, (pHV_SBD == null ? DBNull.Value : (object)pHV_SBD), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ViewDDTSH_KQSH>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_KQSH_HOCVIEN(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pKH_LOP_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pHV_SBD
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pKH_LOP_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pHV_SBD
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        public int GPS_DELETE_SBD_SATHACH(string pDTSH_TTSH_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDTSH_TTSH_MA = new OracleParameter("pDTSH_TTSH_MA", OracleDbType.Varchar2, pDTSH_TTSH_MA, ParameterDirection.Input);
            var datas = _db.DbContext.Database.ExecuteSqlCommand(@"BEGIN GPS_HOCVIEN.GPS_DELETE_SBD_SATHACH(:pDTSH_TTSH_MA
                                                                                                            ); END;"
                                                                                          , _pDTSH_TTSH_MA
                                                                                        );
            return datas;
        }

        /// <summary>
        /// HỌc viên cấp SBD 
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDHV> GPS_GETDS_HOCVIEN_SBD_SATHACH(string pDVGTVT_MA, string pKH_MA, string pKH_LOP_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND, string pIS_SBD)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pIS_SBD = new OracleParameter("pIS_SBD", OracleDbType.Varchar2, (pIS_SBD == null ? DBNull.Value : (object)pIS_SBD), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_SBD_SATHACH(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pKH_LOP_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pIS_SBD
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pKH_LOP_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pIS_SBD
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// Học viên thi sát hạch
        /// </summary>
        /// <param name="pDVGTVT_MA"></param>
        /// <param name="pKH_MA"></param>
        /// <param name="pKH_LOP_MA"></param>
        /// <param name="pHV_MA"></param>
        /// <param name="pHV_HOTEN"></param>
        /// <param name="pHV_CMND"></param>
        /// <returns></returns>
        public List<ModelDHV> GPS_GETDS_HOCVIEN_DU_SATHACH(string pDVGTVT_MA, string pKH_MA, string pKH_LOP_MA, string pHV_MA, string pHV_HOTEN, string pHV_CMND)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, pDVGTVT_MA, ParameterDirection.Input);
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, pKH_MA, ParameterDirection.Input);
            var _pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, pKH_LOP_MA, ParameterDirection.Input);
            var _pHV_MA = new OracleParameter("pHV_MA", OracleDbType.Varchar2, (pHV_MA == null ? DBNull.Value : (object)pHV_MA), ParameterDirection.Input);
            var _pHV_HOTEN = new OracleParameter("pHV_HOTEN", OracleDbType.Varchar2, (pHV_HOTEN == null ? DBNull.Value : (object)pHV_HOTEN), ParameterDirection.Input);
            var _pHV_CMND = new OracleParameter("pHV_CMND", OracleDbType.Varchar2, (pHV_CMND == null ? DBNull.Value : (object)pHV_CMND), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_HOCVIEN_DU_SATHACH(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pKH_LOP_MA
                                                                                                            ,:pHV_MA
                                                                                                            ,:pHV_HOTEN
                                                                                                            ,:pHV_CMND
                                                                                                            ,:pRET); END;"
                                                                                          , _pDVGTVT_MA
                                                                                          , _pKH_MA
                                                                                          , _pKH_LOP_MA
                                                                                          , _pHV_MA
                                                                                          , _pHV_HOTEN
                                                                                          , _pHV_CMND
                                                                                          , _pRET
                                                                                        ).ToList();
            return datas;
        }



        /// <summary>
        /// Get học viên của 1 lớp
        /// </summary>
        /// <param name="Ma_DVGT"></param>
        /// <param name="ma_KH"></param>
        /// <param name="ma_LOP"></param>
        /// <returns></returns>
        public List<ModelDHV> GetdataHocVienInLop(string Ma_DVGT, string ma_KH, string ma_LOP)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, Ma_DVGT, ParameterDirection.Input);
            var pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, ma_KH, ParameterDirection.Input);
            var pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, ma_LOP, ParameterDirection.Input);
            var pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<ModelDHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_LOPHOC_HOCVIEN(:pDVGTVT_MA
                                                                                                                    ,:pKH_MA
                                                                                                                    ,:pKH_LOP_MA
                                                                                                                    ,:pRET); END;"
                                                                                          , pDVGTVT_MA
                                                                                          , pKH_MA
                                                                                          , pKH_LOP_MA
                                                                                          , pRET
                                                                                        ).ToList();
            return datas;
        }

        /// <summary>
        /// Get học viên của phương tiện
        /// </summary>
        /// <param name="Ma_DVGT"></param>
        /// <param name="ma_KH"></param>
        /// <param name="ma_LOP"></param>
        /// <param name="ma_PT"></param>
        /// <param name="ma_LH"></param>
        /// <returns></returns>
        public List<DHV> GetdataHocVienInPhuongTien(string Ma_DVGT, string ma_KH, string ma_LOP, string ma_PT, string ma_LH)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;

            var pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, Ma_DVGT, ParameterDirection.Input);
            var pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, ma_KH, ParameterDirection.Input);
            var pKH_LOP_MA = new OracleParameter("pKH_LOP_MA", OracleDbType.Varchar2, ma_LOP, ParameterDirection.Input);
            var pKH_LOP_PT_MA = new OracleParameter("pKH_LOP_PT_MA", OracleDbType.Varchar2, ma_PT, ParameterDirection.Input);
            var pKH_LOP_LH_MA = new OracleParameter("pKH_LOP_LH_MA", OracleDbType.Varchar2, ma_LH, ParameterDirection.Input);
            var pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<DHV>(@"BEGIN GPS_HOCVIEN.GPS_GETDS_LOPHOC_PHUONGTIEN(:pDVGTVT_MA
                                                                                                            ,:pKH_MA
                                                                                                            ,:pKH_LOP_MA
                                                                                                            ,:pKH_LOP_PT_MA
                                                                                                            ,:pKH_LOP_LH_MA
                                                                                                            ,:pRET); END;"
                                                                                          , pDVGTVT_MA
                                                                                          , pKH_MA
                                                                                          , pKH_LOP_MA
                                                                                          , pKH_LOP_PT_MA
                                                                                          , pKH_LOP_LH_MA
                                                                                          , pRET
                                                                                        ).ToList();
            return datas;
        }
    }
}